package com.ruoyi.common.utils.file;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.DocumentFactoryHelper;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;

import org.apache.poi.ss.util.SheetUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.nio.charset.Charset;
import java.nio.file.*;
import java.util.*;
import java.util.function.Consumer;
import java.util.zip.*;

public class ZipExcelUtil {

    /**
     * 处理压缩包内所有 Excel
     * @param zipPath      原始 zip 绝对路径
     * @param outDir       输出目录（会生成同名新 zip）
     * @param rowConsumer  行级回调，可随意改 map<列名, 单元格值>
     * @return 新 zip 绝对路径
     */
    public static String unzipAndProcessExcel(String zipPath, String outDir, Consumer<Map<Integer, String>> rowConsumer) throws IOException {
        File outFolder = new File(outDir);
        if (!outFolder.exists()) outFolder.mkdirs();

        String newZipName = UUID.randomUUID() + ".zip";
        File newZipFile = new File(outFolder, newZipName);
        ZipFile zip = null;
        try {
            zip = new ZipFile(zipPath, Charset.forName("GBK"));
            try (ZipOutputStream zos = new ZipOutputStream(Files.newOutputStream(newZipFile.toPath()))) {

            Enumeration<? extends ZipEntry> entries = zip.entries();
            while (entries.hasMoreElements()) {
                ZipEntry entry = entries.nextElement();
                if (entry.isDirectory()) continue;

                zos.putNextEntry(new ZipEntry(entry.getName()));

                if (entry.getName().toLowerCase().matches(".+\\.(xlsx?)$")) {
                    // 是 Excel → 读-改-写
                    try (InputStream is = zip.getInputStream(entry)) {
                        processSingleExcel(is, zos, rowConsumer);
                    }
                } else {
                    // 非 Excel 原样复制
                    try (InputStream is = zip.getInputStream(entry)) {
                        org.apache.commons.io.IOUtils.copy(is, zos);
                    }
                }
                zos.closeEntry();
            }
            }
        } finally {
            // 关键：等全部迭代完再关闭 zip
            if (zip != null) {
                zip.close();
            }
        }
        return newZipFile.getAbsolutePath();
    }


    private static void processSingleExcel(InputStream in, OutputStream out,
                                           Consumer<Map<Integer, String>> rowConsumer) throws IOException {

        Workbook srcWb = WorkbookFactory.create(in);
        Workbook destWb = srcWb instanceof XSSFWorkbook ? new XSSFWorkbook() : new HSSFWorkbook();
        DataFormatter formatter = new DataFormatter();

        /* 1. 一次性克隆全部 Sheet（含公式、边框、合并）*/
        for (int i = 0; i < srcWb.getNumberOfSheets(); i++) {
            Sheet srcSheet = srcWb.getSheetAt(i);
            Sheet destSheet = destWb.createSheet(srcSheet.getSheetName());
            deepCopySheet(srcSheet, destSheet);
        }

        /* 2. 统一重算所有公式（跨 Sheet 引用关键）*/
        FormulaEvaluator evaluator = destWb.getCreationHelper().createFormulaEvaluator();
        evaluator.evaluateAll();
        destWb.setForceFormulaRecalculation(true);

        /* 3. 逐 Sheet 局部改值（按物理列号读写）*/
        for (int i = 0; i < destWb.getNumberOfSheets(); i++) {
            Sheet destSheet = destWb.getSheetAt(i);
            Row headerRow = destSheet.getRow(destSheet.getFirstRowNum());
            if (headerRow == null) continue;

            /* 3.1 读表头 <物理列号, 表头文本> */
            Map<Integer, String> headMap = new LinkedHashMap<>();
            for (Cell c : headerRow) {
                if (c != null) headMap.put(c.getColumnIndex(), formatter.formatCellValue(c).trim());
            }

            /* 3.2 业务改值（仅数据行）*/
            for (int r = headerRow.getRowNum() + 1; r <= destSheet.getLastRowNum(); r++) {
                Row row = destSheet.getRow(r);
                if (row == null) continue;

                Map<Integer, String> rowMap = new LinkedHashMap<>();
                for (Map.Entry<Integer, String> e : headMap.entrySet()) {
                    int colIdx = e.getKey();
                    Cell cell = row.getCell(colIdx, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
                    rowMap.put(colIdx, formatter.formatCellValue(cell));
                }

                rowConsumer.accept(rowMap);   // 只改你要的列

                /* 写回：空值 → 空单元格（setBlank），文本数字 → 数值，其余文本 → 原样 */
                for (Map.Entry<Integer, String> e : headMap.entrySet()) {
                    int colIdx = e.getKey();
                    String v  = rowMap.get(colIdx);
                    Cell cell = row.getCell(colIdx);

                    if (v == null || v.trim().isEmpty()) {
                        cell.setBlank();                           // 关键：空单元格，公式不报错
                    } else if (isNumeric(v)) {
                        cell.setCellValue(Double.parseDouble(v));  // 文本数字转数值
                    } else {
                        cell.setCellValue(v);                      // 纯文本
                    }
                }
            }
        }

        /* 4. 写出 */
        srcWb.close();
        destWb.write(out);
        destWb.close();
    }

    private static boolean isNumeric(String str) {
        if (str == null) return false;
        return str.trim().matches("-?\\d+(\\.\\d+)?");
    }


    private static void deepCopySheet(Sheet srcSheet, Sheet destSheet) {
        /* 0. 只给「原表使用范围」刷细线（避免空白区域有线） */
        CellStyle thinStyle = destSheet.getWorkbook().createCellStyle();
        thinStyle.setBorderTop(BorderStyle.THIN);
        thinStyle.setBorderBottom(BorderStyle.THIN);
        thinStyle.setBorderLeft(BorderStyle.THIN);
        thinStyle.setBorderRight(BorderStyle.THIN);

        /* 0.1 计算原表真实使用矩形范围 */
        int firstRow = srcSheet.getFirstRowNum();
        int lastRow  = srcSheet.getLastRowNum();
        int lastCol  = 0;
        for (int r = firstRow; r <= lastRow; r++) {
            Row row = srcSheet.getRow(r);
            if (row != null && row.getLastCellNum() > lastCol) {
                lastCol = row.getLastCellNum();
            }
        }

        /* 0.2 只在这个范围内刷细线 */
        for (int r = firstRow; r <= lastRow; r++) {
            Row destRow = destSheet.createRow(r);
            for (int c = 0; c <= lastCol; c++) {
                Cell cell = destRow.createCell(c);
                cell.setCellStyle(thinStyle);   // 先统一细线
            }
        }

        /* 1. 有值单元格再刷原样式（覆盖细线，保留原边框） */
        for (int r = srcSheet.getFirstRowNum(); r <= srcSheet.getLastRowNum(); r++) {
            Row srcRow = srcSheet.getRow(r);
            Row destRow = destSheet.getRow(r);
            if (srcRow == null) continue;
            for (int c = 0; c < srcRow.getLastCellNum(); c++) {
                Cell srcCell = srcRow.getCell(c, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
                if (srcCell == null) continue;

                Cell destCell = destRow.getCell(c);   // 已存在（细线）
                CellStyle srcStyle = srcCell.getCellStyle();
                CellStyle newStyle = destSheet.getWorkbook().createCellStyle();
                newStyle.cloneStyleFrom(srcStyle);    // 原边框覆盖细线
                destCell.setCellStyle(newStyle);

                /* 数据克隆（含公式） */
                switch (srcCell.getCellType()) {
                    case STRING:
                        destCell.setCellValue(srcCell.getStringCellValue());
                        break;
                    case NUMERIC:
                        if (DateUtil.isCellDateFormatted(srcCell)) {
                            destCell.setCellValue(srcCell.getDateCellValue());
                        } else {
                            destCell.setCellValue(srcCell.getNumericCellValue());
                        }
                        break;
                    case BOOLEAN:
                        destCell.setCellValue(srcCell.getBooleanCellValue());
                        break;
                    case FORMULA:
                        destCell.setCellFormula(srcCell.getCellFormula());
                        break;
                    default:
                        destCell.setCellValue(new DataFormatter().formatCellValue(srcCell));
                }
            }
        }

        /* 2. 合并区域（最后做，顺序不能反） */
        for (int m = 0; m < srcSheet.getNumMergedRegions(); m++) {
            CellRangeAddress region = srcSheet.getMergedRegion(m);
            destSheet.addMergedRegion(region);
            /* 复制原边框（或细线）*/
            Cell sample = srcSheet.getRow(region.getFirstRow()).getCell(region.getFirstColumn());
            BorderStyle bs = (sample != null && sample.getCellStyle() != null)
                    ? BorderStyle.valueOf(String.valueOf(sample.getCellStyle().getBorderTop()))
                    : BorderStyle.THIN;
            RegionUtil.setBorderTop(bs, region, destSheet);
            RegionUtil.setBorderBottom(bs, region, destSheet);
            RegionUtil.setBorderLeft(bs, region, destSheet);
            RegionUtil.setBorderRight(bs, region, destSheet);
        }

        /* 3. 列宽复制 */
        for (int c = 0; c <= srcSheet.getRow(srcSheet.getFirstRowNum()).getLastCellNum(); c++) {
            destSheet.setColumnWidth(c, srcSheet.getColumnWidth(c));
        }
    }
}
